package JDBCPre;

import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;

public class JDBCPreLearn1 {
    static Connection preconn = null;
    PreparedStatement preparedStatement = null;


    static {
        try {
            preconn = DriverManager.getConnection("jdbc:mysql://localhost:3306/2411", "root", "123456");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Test
    public ArrayList<User> findAll() throws SQLException {

        String sql = "select * from user";
        preparedStatement = preconn.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        ArrayList<User> user = new ArrayList<>();
        while (resultSet.next()) {
            User user1 = new User();
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String password = resultSet.getString("password");
            String nickname = resultSet.getString("nickname");
            System.out.println("id = " + id);
            System.out.println("name = " + name);
            System.out.println("password = " + password);
            System.out.println("nickname = " + nickname);
            user1.setId(id);
            user1.setName(name);
            user1.setPassword(password);
            user1.setNickname(nickname);
            user.add(user1);
        }
        return user;

    }

    public void insert(User obj) throws SQLException {

        String sql = "insert into user values (?,?,?,?)";
        preparedStatement = preconn.prepareStatement(sql);

        preparedStatement.setInt(1, obj.getId());
        preparedStatement.setString(2, obj.getName());
        preparedStatement.setString(3, obj.getPassword());
        preparedStatement.setString(4, obj.getNickname());

        int i = preparedStatement.executeUpdate();
        System.out.println(i);
        close();

    }

    public void update(User obj, int id) throws SQLException {

        String sql = "update user set name=?,password=?,nickname=? where id=?";
        preparedStatement = preconn.prepareStatement(sql);

        preparedStatement.setString(1, obj.getName());
        preparedStatement.setString(2, obj.getPassword());
        preparedStatement.setString(3, obj.getNickname());
        preparedStatement.setInt(4, id);

        int i = preparedStatement.executeUpdate();
        System.out.println("i = " + i);
        close();
    }

    public void delete(User obj) throws SQLException {

        String sql = "delete from user where id=?";
        preparedStatement = preconn.prepareStatement(sql);
        preparedStatement.setInt(1, obj.getId());

        int i = preparedStatement.executeUpdate();
        System.out.println("i = " + i);
        close();
    }

    public User findall1(int id) throws SQLException {
        String sql = "select * from user where id=?";
        preparedStatement = preconn.prepareStatement(sql);
        preparedStatement.setInt(1, id);

        ResultSet resultSet = preparedStatement.executeQuery();

        User user1 = new User();
        while (resultSet.next()) {
            String name = resultSet.getString("name");
            String password = resultSet.getString("password");
            String nickname = resultSet.getString("nickname");

            System.out.println("name = " + name);
            System.out.println("password = " + password);
            System.out.println("nickname = " + nickname);

            user1.setName(name);
            user1.setPassword(password);
            user1.setNickname(nickname);

        }
        return user1;
    }

    //测试回滚
    public void delUSer1(Integer id)  {
        try {
            String sql ="delete from user where id=?";
            preconn.setAutoCommit(false);//开启了事务
            preparedStatement = preconn.prepareStatement(sql);
            preparedStatement.setInt(1, id);

            int i = preparedStatement.executeUpdate();
            int y=1/0;
            preconn.commit();


        } catch (Exception e) {
            System.out.println("异常，撤销回滚");
            try {
                preconn.rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
        }

    }

    public void close() throws SQLException {
        preconn.close();
        preparedStatement.close();
    }

}